﻿using log4net;
using Sunny.UI;
using System.Diagnostics.CodeAnalysis;
using Excel = Microsoft.Office.Interop.Excel;

namespace FZRD

{
    public partial class Page3 : UIPage
    {
        private static readonly ILog log = LogManager.GetLogger(typeof(Page3));

        private List<SensorAlarmEntity> entityList = new List<SensorAlarmEntity>();
        //X轴值数量初始值
        private long xCount = 1000;

        private string alarmFilePath = "D:\\聚芯光纤\\";

        private string alarmFileName = "告警数据";

        private string alarmFileNameDate = "";

        [DisallowNull]
        private Excel.Application excelApp;

        [DisallowNull]
        private Excel._Workbook workbook;

        public Page3()
        {
            InitializeComponent();
            uiDataGridView1.AddColumn("告警ID", "ID").SetFixedMode(100);
            uiDataGridView1.AddColumn("告警时间", "AlarmDateTime").SetFixedMode(150);
            uiDataGridView1.AddColumn("传感器ID", "SensorID").SetFixedMode(100);
            uiDataGridView1.AddColumn("传感器名", "SensorName").SetFixedMode(150);
            uiDataGridView1.AddColumn("通道号", "ChannelIndex").SetFixedMode(100);
            uiDataGridView1.AddColumn("传感器号", "SensorIndex").SetFixedMode(100);
            uiDataGridView1.AddColumn("波长值", "WaveLength").SetFixedMode(100);
            uiDataGridView1.AddColumn("温度值", "Temperature").SetFixedMode(100);
            uiDataGridView1.AddColumn("描述", "Description").SetFixedMode(500);
            uiDataGridView1.AddColumn("通知邮箱", "Email").SetFixedMode(200);
            uiDataGridView1.AddColumn("通知短信", "SMS").SetFixedMode(200);
        }
        public override void Init()
        {
            base.Init();

            RefreshTable();

        }
        public void RefreshTable()
        {
            entityList.Clear();

            try
            {
                var sql = @"select * from sensor_alarm_notice where 1=1 ";
                sql = sql + @" and AlarmTime > " + DateTimeOffset.UtcNow.AddDays(-1).ToUnixTimeMilliseconds() + @" and AlarmTime < " + DateTimeOffset.UtcNow.ToUnixTimeMilliseconds() + @" order by AlarmTime limit " + this.xCount + @";";
                var dt = MainForm.db.SelectDataTable(sql);

                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    var row = dt.Rows[i];
                    var func = new SensorAlarmEntity()
                    {
                        ID = (int)row["ID"],
                        AlarmTime = row["AlarmTime"] != DBNull.Value ? (long)row["AlarmTime"] : 0,
                        AlarmtDateTime = row["AlarmDateTime"] != DBNull.Value ? row["AlarmDateTime"].ToString() : "",
                        SensorID = row["SensorID"] != DBNull.Value ? (int)row["SensorID"] : 0,
                        SensorName = row["SensorName"] != DBNull.Value ? row["SensorName"].ToString() : "",
                        ChannelIndex = row["ChannelIndex"] != DBNull.Value ? (int)row["ChannelIndex"] : 0,
                        SensorIndex = row["SensorIndex"] != DBNull.Value ? (int)row["SensorIndex"] : 0,
                        WaveLength = row["WaveLength"] != DBNull.Value ? (double)row["WaveLength"] : 0,
                        Temperature = row["Temperature"] != DBNull.Value ? (double)row["Temperature"] : 0,
                        Description = row["Description"] != DBNull.Value ? row["Description"].ToString() : "",
                        Email = row["Email"] != DBNull.Value ? row["Email"].ToString() : "",
                        SMS = row["SMS"] != DBNull.Value ? row["SMS"].ToString() : ""
                    };
                    entityList.Add(func);
                }

                if (entityList.Count > 0)
                {
                    uiPagination1.DataSource = entityList;
                    uiPagination1.ActivePage = 1;
                }
            }
            catch (Exception ex)
            {
                log.Error(ex.Message);
            }
        }
        private void uiPagination1_PageChanged(object sender, object pagingSource, int pageIndex, int count)
        {
            uiDataGridView1.DataSource = pagingSource;
        }

        private void btnQuery_Click(object sender, EventArgs e)
        {
            DateTime startTime = startDatetime.Value;
            DateTime endTime = endDatetime.Value;
            entityList.Clear();
            try
            {
                var sql = @"select * from sensor_alarm_notice where 1=1 ";
                sql = sql + @" and AlarmTime > " + ToUnixTimestampMilliseconds(startTime) + @" and AlarmTime < " + ToUnixTimestampMilliseconds(endTime) + @" order by AlarmTime limit " + this.xCount + @";";
                var dt = MainForm.db.SelectDataTable(sql);
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    var row = dt.Rows[i];
                    var func = new SensorAlarmEntity()
                    {
                        ID = (int)row["ID"],
                        AlarmTime = row["AlarmTime"] != DBNull.Value ? (long)row["AlarmTime"] : 0,
                        AlarmtDateTime = row["AlarmDateTime"] != DBNull.Value ? row["AlarmDateTime"].ToString() : "",
                        SensorID = row["SensorID"] != DBNull.Value ? (int)row["SensorID"] : 0,
                        SensorName = row["SensorName"] != DBNull.Value ? row["SensorName"].ToString() : "",
                        ChannelIndex = row["ChannelIndex"] != DBNull.Value ? (int)row["ChannelIndex"] : 0,
                        SensorIndex = row["SensorIndex"] != DBNull.Value ? (int)row["SensorIndex"] : 0,
                        WaveLength = row["WaveLength"] != DBNull.Value ? (double)row["WaveLength"] : 0,
                        Temperature = row["Temperature"] != DBNull.Value ? (double)row["Temperature"] : 0,
                        Description = row["Description"] != DBNull.Value ? row["Description"].ToString() : "",
                        Email = row["Email"] != DBNull.Value ? row["Email"].ToString() : "",
                        SMS = row["SMS"] != DBNull.Value ? row["SMS"].ToString() : ""
                    };
                    entityList.Add(func);
                }
                if (entityList.Count > 0)
                {
                    uiPagination1.DataSource = entityList;
                    uiPagination1.ActivePage = 1;
                }

                RecordUserOperation("告警记录查询 " + " startTime=" + startTime.ToString("yyyy-MM-dd HH:mm:ss") + " startTime=" + endTime.ToString("yyyy-MM-dd HH:mm:ss"));
            }
            catch (Exception ex)
            {
                log.Error(ex.Message);
            }
        }

        private void btnExport_Click(object sender, EventArgs e)
        {
            SaveSensorAlarmDataToExcel(entityList);
        }

        public static long ToUnixTimestamp(DateTime dateTime)
        {
            return (long)Math.Floor((dateTime.ToUniversalTime() - new DateTime(1970, 1, 1, 0, 0, 0, DateTimeKind.Utc)).TotalSeconds);
        }

        public static long ToUnixTimestampMilliseconds(DateTime dateTime)
        {
            return (long)(dateTime.ToUniversalTime() - new DateTime(1970, 1, 1, 0, 0, 0, DateTimeKind.Utc)).TotalMilliseconds;
        }

        //数据保存到excel
        public void SaveSensorAlarmDataToExcel(List<SensorAlarmEntity> sensorAlarmDatas)
        {
            if (this.excelApp == null)
            {
                CreateExcelDataFile();
            }
            if (this.workbook == null)
            {
                MessageBox.Show("无法打开 Workbook 对象，请确认 Excel 文件是否正常");
                return;

            }
            DateTime dt = DateTime.Now;
            int hour = dt.Hour;
            int min = dt.Minute;
            int sec = dt.Second;
            int msec = dt.Millisecond;
            string date = dt.Year.ToString("D4") + dt.Month.ToString("D2") + dt.Day.ToString("D2") + dt.Hour.ToString("D2") + dt.Minute.ToString("D2") + dt.Second.ToString("D2");
            string filePath;
            if (alarmFilePath == "")
            {
                filePath = AppDomain.CurrentDomain.BaseDirectory + alarmFileName + @"-" + date + @".xlsx";
            }
            else
            {
                filePath = alarmFilePath + alarmFileName + @"-" + date + @".xlsx";
            }
            if (!alarmFileNameDate.Equals(date))
            {
                this.workbook.Close(false);
                this.workbook = null;
                //excelApp.Quit();
                //excelApp = null;
                Excel.Workbooks workbooks = this.excelApp.Workbooks;
                if (!File.Exists(filePath))
                {
                    File.Copy(AppDomain.CurrentDomain.BaseDirectory + alarmFileName + @".xlsx", filePath, true);
                }
                this.workbook = workbooks.Add(filePath); //加载模板
                alarmFileNameDate = date;
            }

            Excel.Sheets sheets = workbook.Sheets;
            Excel.Worksheet worksheet = (Excel.Worksheet)sheets.get_Item(1); //第一个工作薄。
            if (worksheet == null)
            {
                MessageBox.Show("无法打开 Worksheet 对象，请确认 Excel 文件是否正常");
                return; //工作薄中没有工作表.
            }

            int columnIndex = 1; // A列的索引是1
            int columnCount = CountColumnValues(worksheet, columnIndex);
            if ((sensorAlarmDatas != null) && (sensorAlarmDatas.Count > 0))
            {
                //写入数值
                for (int i = 0; i < sensorAlarmDatas.Count; i++)
                {
                    worksheet.Cells[columnCount + 1 + i, 1] = sensorAlarmDatas[i].ID;
                    worksheet.Cells[columnCount + 1 + i, 2] = sensorAlarmDatas[i].AlarmTime;
                    worksheet.Cells[columnCount + 1 + i, 3] = sensorAlarmDatas[i].AlarmtDateTime;
                    worksheet.Cells[columnCount + 1 + i, 4] = sensorAlarmDatas[i].SensorID;
                    worksheet.Cells[columnCount + 1 + i, 5] = sensorAlarmDatas[i].SensorName;
                    worksheet.Cells[columnCount + 1 + i, 6] = sensorAlarmDatas[i].ChannelIndex;
                    worksheet.Cells[columnCount + 1 + i, 7] = sensorAlarmDatas[i].SensorIndex;
                    worksheet.Cells[columnCount + 1 + i, 8] = sensorAlarmDatas[i].WaveLength;
                    worksheet.Cells[columnCount + 1 + i, 9] = sensorAlarmDatas[i].Temperature;
                    worksheet.Cells[columnCount + 1 + i, 10] = sensorAlarmDatas[i].Description;
                    worksheet.Cells[columnCount + 1 + i, 11] = sensorAlarmDatas[i].Email;
                    worksheet.Cells[columnCount + 1 + i, 12] = sensorAlarmDatas[i].SMS;
                }
                worksheet.Columns.EntireColumn.AutoFit();//列宽自适应
            }
            try
            {
                this.workbook.Saved = true;
                this.workbook.SaveAs(filePath);
            }
            catch (Exception ex)
            {
                bool isUnlocked = FileUnlocker.UnlockFile(filePath);
                if (isUnlocked)
                {
                    this.workbook.SaveAs(filePath);
                }
                else
                {
                    MessageBox.Show("存储 Excel 文件时出错,文件可能正被打开！\n" + ex.Message);
                    log.Error(ex.Message);
                    return;
                }
            }
            RecordUserOperation("导出告警记录 filePath=" + filePath);
        }

        private int CountColumnValues(Excel.Worksheet worksheet, int columnIndex)
        {
            int valueCount = 0;
            Excel.Range usedRange = worksheet.UsedRange;

            // 获取列的范围
            Excel.Range columnRange = (Excel.Range)worksheet.Columns[columnIndex];
            int lastRow = columnRange.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row;

            // 遍历列中的所有单元格
            for (int row = 1; row <= lastRow; row++)
            {
                Excel.Range cell = (Excel.Range)worksheet.Cells[row, columnIndex];
                if (cell.Value2 != null && !cell.Value2.ToString().Trim().Equals(""))
                {
                    valueCount++;
                }
            }

            System.Runtime.InteropServices.Marshal.ReleaseComObject(columnRange);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(usedRange);

            return valueCount;
        }


        private void CreateExcelDataFile()
        {
            // 创建Excel应用程序实例
            this.excelApp = new Excel.Application();
            this.excelApp.Visible = false;
            this.excelApp.UserControl = true;
            this.excelApp.DisplayAlerts = false;

            DateTime dt = DateTime.Now;
            string date = dt.Year.ToString("D4") + dt.Month.ToString("D2") + dt.Day.ToString("D2") + dt.Hour.ToString("D2") + dt.Minute.ToString("D2") + dt.Second.ToString("D2");
            string filePath;
            if (alarmFilePath == "")
            {
                filePath = AppDomain.CurrentDomain.BaseDirectory + alarmFileName + @"-" + date + @".xlsx";
            }
            else
            {
                filePath = alarmFilePath + alarmFileName + @"-" + date + @".xlsx";
            }
            Excel.Workbooks workbooks = this.excelApp.Workbooks;
            try
            {
                if (!File.Exists(filePath))
                {
                    File.Copy(AppDomain.CurrentDomain.BaseDirectory + alarmFileName + @".xlsx", filePath, true);
                }
                this.workbook = workbooks.Add(filePath); //加载模板
            }
            catch (Exception ex)
            {
                bool isUnlocked = FileUnlocker.UnlockFile(filePath);
                if (isUnlocked)
                {
                    this.workbook = workbooks.Add(filePath); //加载模板
                }
                else
                {
                    MessageBox.Show("存储 Excel 文件时出错,文件可能正被打开！\n" + ex.Message);
                    log.Error(ex.Message);
                    return;
                }
            }
            alarmFileNameDate = date;
        }

        /*
        private void ReadExcelDataFile()
        {
            // 创建Excel应用程序实例
            this.excelApp = new Excel.Application();
            if (this.excelApp == null)
            {
                MessageBox.Show("无法创建excel对象，请确认excel环境是否正常");
                return;

            }
            this.excelApp.Visible = false;
            this.excelApp.UserControl = true;
            this.excelApp.DisplayAlerts = false;

            DateTime dt = DateTime.Today; // 获取今天的日期
            //DateTime yesterday = dt.AddDays(-1); // 获取前一天的日期

            string date = dt.Year.ToString("D4") + dt.Month.ToString("D2") + dt.Day.ToString("D2") + dt.Hour.ToString("D2") + dt.Minute.ToString("D2") + dt.Second.ToString("D2");
            string filePath;
            if (alarmFilePath == "")
            {
                filePath = AppDomain.CurrentDomain.BaseDirectory + alarmFileName + @"-" + date + @".xlsx";
            }
            else
            {
                filePath = alarmFilePath + alarmFileName + @"-" + date + @".xlsx";
            }
            Excel.Workbooks workbooks = this.excelApp.Workbooks;
            try
            {
                if (File.Exists(filePath))
                {
                    this.workbook = workbooks.Add(filePath); //加载模板
                }
                else
                {
                    File.Copy(AppDomain.CurrentDomain.BaseDirectory + alarmFileName + @".xlsx", filePath, true);
                    this.workbook = workbooks.Add(filePath); //加载模板
                }
            }
            catch (Exception ex)
            {
                bool isUnlocked = FileUnlocker.UnlockFile(filePath);
                if (isUnlocked)
                {
                    this.workbook = workbooks.Add(filePath); //加载模板
                }
                else
                {
                    MessageBox.Show("存储 Excel 文件时出错,文件可能正被打开！\n" + ex.Message);
                    log.Error(ex.ToString());
                    return;
                }
            }
        }
        */

        public void RecordUserOperation(string operation)
        {
            try
            {
                string userName = LoginForm.username;
                string userIp = "127.0.0.1";
                long recordTime = ((DateTimeOffset)DateTime.UtcNow).ToUnixTimeMilliseconds(); ;
                MainForm.db.ExecuteSql(
                    MainForm.db.Insert("system_operation_content")
                        .SetColumn("UserName", userName)
                        .SetColumn("UserIP", userIp)
                        .SetColumn("RecordTime", recordTime)
                        .SetColumn("Operation", operation)
                        .ToSql());
            }
            catch (Exception ex)
            {
                log.Error(ex.Message);
            }
        }
    }

}